﻿using CK.Sprite.Form.Core;
using CK.Sprite.Framework;
using Dapper;
using Dapper.Contrib.Extensions;
using JetBrains.Annotations;
using Newtonsoft.Json.Linq;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace CK.Sprite.Form.MySql
{
    public class BaseSpriteRepository<T> : GuidRepositoryBase<T> where T : class
    {
        public BaseSpriteRepository(IUnitOfWork unitOfWork) : base(unitOfWork) { }

        public async Task<string> GetApplicationCodeAsync(string tableName, Guid id)
        {
            string strSql = $"SELECT ApplicationCode FROM {tableName} WHERE Id=@Id;";
            return await _unitOfWork.Connection.QueryFirstOrDefaultAsync<string>(strSql, new { Id = id }, _unitOfWork.Transaction);
        }
        public async Task<List<string>> GetCategorysAsync(string tableName)
        {
            string strSql = $"SELECT Category FROM {tableName} WHERE Category IS NOT NULL AND Category <> '' GROUP BY Category ORDER BY Category;";
            var results = await _unitOfWork.Connection.QueryAsync<string>(strSql, _unitOfWork.Transaction);
            return results.ToList();
        }

        protected string CreateConditionSql(JObject sqlWhereParamValues, ExpressSqlModel expressSqlModel, ref int index)
        {
            var conditionType = expressSqlModel.ConditionType;
            var field = expressSqlModel.Field;
            StringBuilder sbSqlWhere = new StringBuilder();
            switch (conditionType)
            {
                case EConditionType.等于:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}=@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.Like:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} LIKE CONCAT('%',@SW{index}_{field},'%')");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.In:
                    var inValues = expressSqlModel.Value as JArray;
                    if(inValues.Count > 0)
                    {
                        sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IN @SW{index}_{field}");
                        sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    }
                    else
                    {
                        sbSqlWhere.Append($"1=1");
                        index--;
                    }
                    break;
                case EConditionType.Between:
                    var betweenValues = expressSqlModel.Value as JArray;
                    if(betweenValues.Count > 1)
                    {
                        sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} BETWEEN @SW{index}_{field}_1 AND @SW{index}_{field}_2");
                        sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}_1", betweenValues[0]));
                        sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}_2", betweenValues[1]));
                    }
                    else
                    {
                        sbSqlWhere.Append($"1=1");
                        index--;
                    }
                    break;
                case EConditionType.大于:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}>@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.大于等于:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}>=@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.小于:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.小于等于:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<=@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.不等于:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<>@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.Null:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IS NULL");
                    break;
                case EConditionType.NotNull:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IS NOT NULL");
                    break;
                case EConditionType.NotIn:
                    var notInValues = expressSqlModel.Value as JArray;
                    if (notInValues.Count > 0)
                    {
                        sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} NOT IN @SW{index}_{field}");
                        sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    }
                    else
                    {
                        sbSqlWhere.Append($"1=1");
                        index--;
                    }
                    break;
                default:
                    break;
            }

            index++;

            return sbSqlWhere.ToString();
        }

        protected string CreateSqlWhere(List<QueryWhereModel> queryWhereModels, JObject paramValues)
        {
            if (queryWhereModels == null || queryWhereModels.Count == 0)
            {
                return "1=1";
            }
            StringBuilder sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append("1=1");

            ExpressSqlModel expressSqlModel = new ExpressSqlModel()
            {
                SqlExpressType = ESqlExpressType.And
            };

            expressSqlModel.Children = queryWhereModels.Select(r => new ExpressSqlModel()
            {
                ConditionType = r.ConditionType,
                Field = r.Field,
                SqlExpressType = ESqlExpressType.Condition,
                Value = r.Value
            }).ToList();

            if (expressSqlModel != null)
            {
                string strSql = ExpressSqlHelper.CreateSqlWhere(expressSqlModel, paramValues, CreateConditionSql);
                if (!string.IsNullOrEmpty(strSql))
                {
                    sbSqlWhere.Append($" AND {strSql}");
                }
            }

            return sbSqlWhere.ToString();
        }

        protected string CreateSqlWhere(ExpressSqlModel expressSqlModel, JObject paramValues)
        {
            StringBuilder sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append("1=1");

            if (expressSqlModel != null)
            {
                string strSql = ExpressSqlHelper.CreateSqlWhere(expressSqlModel, paramValues, CreateConditionSql);
                if (!string.IsNullOrEmpty(strSql))
                {
                    sbSqlWhere.Append($" AND {strSql}");
                }
            }

            return sbSqlWhere.ToString();
        }
    }
}
